/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package merlion.crm.slcm.session;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import merlion.common.util.Consts;
import merlion.common.util.exception.NotExistException;
import merlion.crm.slcm.entity.Customer;
import merlion.crm.slcm.entity.MemberDiscountGroup;
import merlion.crm.slcm.entity.OrderDiscountGroup;
import merlion.crm.slcm.entity.SalesLead;
import merlion.crm.slcm.pojo.Analytics;
import merlion.crm.sopm.entity.ItemLine;
import merlion.frm.bpm.entity.Invoice;
import merlion.frm.ccm.entity.CreditGroup;
import merlion.mrp.ppm.entity.Product;

/**
 *
 * @author Yuwei
 */
@Stateless
public class SLCSessionBean implements SLCSessionBeanLocal, SLCSessionBeanRemote {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public MemberDiscountGroup getCustomerMemberDiscountGroup(Customer customer) {

        if (customer != null) {
            Query query = entityManager.createQuery("SELECT c FROM Customer c WHERE c.clientId LIKE :p1");
            query.setParameter("p1", customer.getClientId());

            List<Customer> list = query.getResultList();
            Customer c = list.get(0);
            MemberDiscountGroup mdg = c.getMemberDiscountGroup();

            return mdg;
        }
        
        return null;
    }

    @Override
    public OrderDiscountGroup getOrderDiscountGroup(ItemLine il) {

        String prodType = il.getProduct().getProductType();
        Long quantity = il.getQtyOrderedBar();

        // Find order discount using il
        Query discountQuery = entityManager.createQuery("SELECT d FROM OrderDiscountGroup d WHERE d.product.productType LIKE :p1 AND d.quantity < :p2 ORDER BY d.quantity DESC");
        discountQuery.setParameter("p1", prodType);
        discountQuery.setParameter("p2", quantity);
        OrderDiscountGroup odg = null;
        List<OrderDiscountGroup> list = discountQuery.getResultList();
        if (!list.isEmpty()) {
            odg = list.get(0);
        } else {
            odg = new OrderDiscountGroup();
            odg.setProduct(il.getProduct());
            odg.setQuantity(quantity);
            odg.setOrderDiscount((double) 0.0);
        }
        System.out.println("ODG Product: " + odg.getProduct().getProductType());
        //System.out.println("ODG Group name: " + odg.getOrderDiscountGroup());
        return odg;
    }

    @Override
    public SalesLead getClientByClientId(String id) throws NotExistException {
        Query query = entityManager.createQuery("SELECT sl FROM SalesLead sl WHERE sl.clientId =:p2");
        query.setParameter("p2", id);
        if (query.getResultList().isEmpty()) {
            throw new NotExistException("Not exist customer");
        }
        SalesLead salesLead = (SalesLead) query.getResultList().get(0);
        return salesLead;
    }

    @Override
    public Customer getCustomerByClientId(String clientId) throws NotExistException {
        Query queryCustomer = entityManager.createQuery("SELECT cus FROM Customer cus WHERE cus.clientId =:p2");
        queryCustomer.setParameter("p2", clientId);
        if (queryCustomer.getResultList().isEmpty()) {
            throw new NotExistException("Not exist customer");
        }
        Customer customer = (Customer) queryCustomer.getResultList().get(0);
        return customer;
    }

    @Override
    public SalesLead getSalesLeadByClientId(String clientId) throws NotExistException {
        Query query = entityManager.createQuery("SELECT s FROM SalesLead s WHERE s.clientId =:p2 AND TYPE(s)=SalesLead");
        query.setParameter("p2", clientId);
        if (query.getResultList().isEmpty()) {
            throw new NotExistException("Not exist sale lead");
        }
        SalesLead salesLead = (SalesLead) query.getResultList().get(0);
        return salesLead;
    }

    @Override
    public SalesLead getClientById(Long id) throws NotExistException {
        Query query = entityManager.createQuery("SELECT sl FROM SalesLead sl WHERE sl.id =:p1");
        query.setParameter("p1", id);
        if (query.getResultList().isEmpty()) {
            throw new NotExistException("Not exist client");
        }
        SalesLead client = (SalesLead) query.getResultList().get(0);
        return client;
    }

    @Override
    public Customer getCustomerById(Long id) throws NotExistException {
        Query queryCustomer = entityManager.createQuery("SELECT cus FROM Customer cus WHERE cus.id =:p2");
        queryCustomer.setParameter("p2", id);
        if (queryCustomer.getResultList().isEmpty()) {
            throw new NotExistException("Not exist customer");
        }
        Customer customer = (Customer) queryCustomer.getResultList().get(0);
        return customer;
    }

    @Override
    public SalesLead getSalesLeadById(Long id) throws NotExistException {
        Query query = entityManager.createQuery("SELECT sl FROM SalesLead sl WHERE sl.id =:p1 AND TYPE(sl)=SalesLead");
        query.setParameter("p1", id);
        if (query.getResultList().isEmpty()) {
            throw new NotExistException("Not exist salesLead");
        }
        SalesLead salesLead = (SalesLead) query.getResultList().get(0);
        return salesLead;
    }

    @Override
    public SalesLead createSalesLead(SalesLead salesLead) {
        entityManager.merge(salesLead);

        Query query = entityManager.createQuery("SELECT s FROM SalesLead s WHERE s.clientId =:p2");
        query.setParameter("p2", salesLead.getClientId());
        salesLead = (SalesLead) query.getResultList().get(0);

        return salesLead;
    }

    @Override
    public List<SalesLead> getAllClient() {
        Query query = entityManager.createQuery("SELECT s FROM SalesLead s");
        return query.getResultList();
    }

    @Override
    public List<SalesLead> getAllSalesLead() {
        Query query = entityManager.createQuery("SELECT s FROM SalesLead s WHERE TYPE(s)=SalesLead");
        return query.getResultList();
    }

    @Override
    public List<Customer> getAllCustomer() {
        //Query query = entityManager.createNamedQuery("getAllCustomer", Customer.class);
        Query query = entityManager.createQuery("SELECT c FROM Customer c");
        return query.getResultList();
    }

    @Override
    public SalesLead updateSalesLead(SalesLead salesLead) {
        entityManager.merge(salesLead);
        return salesLead;
    }

    @Override
    public Customer createCustomer(Customer customer) {

        entityManager.merge(customer);

        Query queryCustomer = entityManager.createQuery("SELECT cus FROM Customer cus WHERE cus.clientId =:p2");
        queryCustomer.setParameter("p2", customer.getClientId());
        customer = (Customer) queryCustomer.getResultList().get(0);

        return customer;
    }

    @Override
    public List<OrderDiscountGroup> getAllOrderDiscountGroup() {
        Query query = entityManager.createNamedQuery("getAllOrderDiscountGroup", OrderDiscountGroup.class);
        return query.getResultList();
    }

    @Override
    public Customer updateCustomer(Customer customer) {

        try {
            Customer oldCustomer = getCustomerById(customer.getId());
            oldCustomer.getMemberDiscountGroup().getCustomers().remove(oldCustomer);
            entityManager.merge(oldCustomer);
            entityManager.flush();
        } catch (Exception e) {
            System.out.print(e.getMessage());
        }
        customer.getMemberDiscountGroup().getCustomers().add(customer);
        entityManager.merge(customer);

        return customer;
    }

    @Override
    public OrderDiscountGroup createOrderDiscountGroup(OrderDiscountGroup odg) {
        System.out.print("#########################" + odg.getId());
        entityManager.merge(odg);
        System.out.print("#########################" + odg.getId());
        return odg;
    }

    @Override
    public List<Product> getAllProduct() {
        Query query = entityManager.createQuery("SELECT p FROM Product p");
        return query.getResultList();
    }

    @Override
    public Product getProductByProductType(String type) {

        Query query = entityManager.createQuery("SELECT p FROM Product p WHERE p.productType LIKE :pdType");
        query.setParameter("pdType", type);
        Product pd = (Product) query.getResultList().get(0);

        return pd;
    }

    @Override
    public Product getProductById(Long id) {

        System.out.println("Session bean Product id: " + id);
        Query query = entityManager.createQuery("SELECT p FROM Product p WHERE p.id = :a1");
        query.setParameter("a1", id);

        List<Product> list = query.getResultList();
        Product pd = list.get(0);

        return pd;
    }

    @Override
    public OrderDiscountGroup getOrderDiscountGroupById(Long id) {

        Query query = entityManager.createQuery("SELECT o FROM OrderDiscountGroup o WHERE o.id =:p1");
        query.setParameter("p1", id);
        OrderDiscountGroup odg = (OrderDiscountGroup) query.getResultList().get(0);

        return odg;
    }

    @Override
    public OrderDiscountGroup updateOrderDiscountGroup(OrderDiscountGroup odg) {
        odg = entityManager.merge(odg);
        return odg;
    }

    @Override
    public List<MemberDiscountGroup> getAllMemberDiscountGroup() {
        Query query = entityManager.createQuery("SELECT m FROM MemberDiscountGroup m");
        return query.getResultList();
    }

    @Override
    public MemberDiscountGroup createMemberDiscountGroup(MemberDiscountGroup mdg) {
        entityManager.persist(mdg);
        return mdg;
    }

    @Override
    public MemberDiscountGroup getMemberDiscountGroupById(Long id) {

        Query query = entityManager.createQuery("SELECT m FROM MemberDiscountGroup m WHERE m.id =:p1");
        query.setParameter("p1", id);
        MemberDiscountGroup mdg = (MemberDiscountGroup) query.getResultList().get(0);

        return mdg;
    }

    @Override
    public MemberDiscountGroup updateMemberDiscountGroup(MemberDiscountGroup mdg) {
        MemberDiscountGroup oldMdg = getMemberDiscountGroupById(mdg.getId());
        mdg = entityManager.merge(mdg);
        //update customers' memberdiscount groups (for example, when accumulated spending decrease, some customers need to be upgraded
        if (oldMdg.getAccSpendingForDs() > mdg.getAccSpendingForDs() || oldMdg.getAccSpendingForWs() > mdg.getAccSpendingForWs()) {
            updateCustomerMemberDiscountGroup(mdg, oldMdg);
        }
        return mdg;
    }

    //#######################################test please#########################
    private void updateCustomerMemberDiscountGroup(MemberDiscountGroup mdg, MemberDiscountGroup oldMdg) {
        //get all affected customers
        if (oldMdg.getAccSpendingForDs() > mdg.getAccSpendingForDs()) { //direct sales customers got affected
            Query query = entityManager.createQuery("SELECT c FROM Customer c WHERE c.Category = :p1 AND c.accumulatedPurchases >= :p2 AND c.accumulatedPurchases < :p3");
            query.setParameter("p1", "DirectSale");
            query.setParameter("p2", mdg.getAccSpendingForDs());
            query.setParameter("p3", oldMdg.getAccSpendingForDs());
            Collection<Customer> customerList = query.getResultList();
            if (!customerList.isEmpty()) {
                for (Customer c : customerList) {
                    c.getMemberDiscountGroup().getCustomers().remove(c); //remove customer from the old group
                    c.setMemberDiscountGroup(mdg); //set new Member Discount Group for the customer
                    mdg.getCustomers().add(c);  //add the customer into the member discount group 
                }
            }
        }

        if (oldMdg.getAccSpendingForWs() > mdg.getAccSpendingForWs()) { //Whole sales customers got affected
            Query query = entityManager.createQuery("SELECT c FROM Customer c WHERE c.Category = :p1 AND c.accumulatedPurchases >= :p2 AND c.accumulatedPurchases < :p3");
            query.setParameter("p1", "WholeSale");
            query.setParameter("p2", mdg.getAccSpendingForWs());
            query.setParameter("p3", oldMdg.getAccSpendingForWs());
            Collection<Customer> customerList = query.getResultList();
            if (!customerList.isEmpty()) {
                for (Customer c : customerList) {
                    c.getMemberDiscountGroup().getCustomers().remove(c); //remove customer from the old group
                    c.setMemberDiscountGroup(mdg); //set new Member Discount Group for the customer
                    mdg.getCustomers().add(c);  //add the customer into the member discount group 
                }
            }
        }

    }

    @Override
    public String generateClientID() {

        //Get date YYYYMMDD
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String frontId = sdf.format(cal.getTime());

        //Query Saleslead table, anything like 'YYYYMMDD'
        Query query = entityManager.createQuery("SELECT s FROM SalesLead s WHERE s.clientId LIKE :p1");
        query.setParameter("p1", frontId + "%");

        //Get result size
        int size = query.getResultList().size() + 1;
        String backId = String.format("%03d", size);

        //Set new ID to 'YYYYMMDD' + 'size + 1'
        String id = frontId + backId;
        //return id
        return id;
    }

    @Override
    public List<CreditGroup> getAllCreditGroup() {
        Query query = entityManager.createQuery("SELECT g FROM CreditGroup g");
        return query.getResultList();
    }

    @Override
    public Customer convertSalesLead(Customer customer, SalesLead salesLead) {
        try {

            //Get Sales Lead
            Query slQuery = entityManager.createQuery("SELECT s FROM SalesLead s WHERE s.id = :p3");
            slQuery.setParameter("p3", salesLead.getId());
            salesLead = (SalesLead) slQuery.getResultList().get(0);

            entityManager.merge(customer);
            entityManager.remove(salesLead);

            return customer;
        } catch (Exception e) {
            System.out.println("Convert sales lead to customer failed: " + e.getMessage());
        }
        return customer;
    }

    @Override
    public Product updateProductPrice(Product product) {
        product.setWsUnitPrice(roundToFourDecimals(product.getBasePrice() * product.getWsMarkup()));
        product.setDsUnitPrice(roundToFourDecimals(product.getBasePrice() * product.getDsMarkup()));
        entityManager.merge(product);
        return product;
    }

    private double roundToFourDecimals(double d) {
        DecimalFormat fourDForm = new DecimalFormat("#.####");
        return Double.valueOf(fourDForm.format(d));
    }

    @Override
    public CreditGroup getCreditGroupbyId(Long id) {
        try {
            CreditGroup cg = (CreditGroup) entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.id = " + id).getResultList().get(0);
            return cg;
        } catch (Exception e) {
            System.out.println("Cannot find credit group: " + e.getMessage());
            return null;
        }
    }

    @Override
    public void removeSalesLead(SalesLead salesLead) {

        //Get Sales Lead
        Query slQuery = entityManager.createQuery("SELECT s FROM SalesLead s WHERE s.id = :p3");
        slQuery.setParameter("p3", salesLead.getId());
        salesLead = (SalesLead) slQuery.getResultList().get(0);

        entityManager.remove(salesLead);
    }

    @Override
    public void updateCustomerMemberDiscountGroup(Invoice invoice) {
        //Method is invoked when Billing and Payment Department creates a new invoice

        Customer customer = invoice.getCustomer();
        customer.setAccumulatedPurchases(customer.getAccumulatedPurchases() + invoice.getTotal());

        String column = "";
        if (customer.getCategory().equals("DirectSale")) {
            column = "accSpendingForDs";
        } else {
            column = "accSpendingForWs";
        }

        //Update member discount group
        Query query = entityManager.createQuery("SELECT m FROM MemberDiscountGroup m WHERE m.:p1 <= :p2 ORDER BY m.:p1 DESC");
        query.setParameter("p1", column);
        query.setParameter("p2", customer.getAccumulatedPurchases());
        Collection<MemberDiscountGroup> list = query.getResultList();
        if (!list.isEmpty()) {
            MemberDiscountGroup mdg = list.iterator().next();
            customer.setMemberDiscountGroup(mdg);
            entityManager.merge(customer);
        }
    }

    @Override
    public List<MemberDiscountGroup> getMemberDiscountGroupByLimitAccSpendingForDs() {
        Query query = entityManager.createQuery("SELECT m FROM MemberDiscountGroup m ORDER BY m.accSpendingForDs ASC");
        List<MemberDiscountGroup> mdg = query.getResultList();
        return mdg;
    }

    @Override
    public void deleteOrderDiscountGroup(Long id) {
        System.out.print("#######################" + "SLCSessionBean: line 385" + id);
        OrderDiscountGroup odg = getOrderDiscountGroupById(id);
        odg.setProduct(null);
        try {
            entityManager.remove(odg);
        } catch (Exception e) {
            System.out.print(e.getMessage());
        }
    }

    @Override
    public List<CreditGroup> getCreditGroupByCreditLimit() {
        Query query = entityManager.createQuery("SELECT c FROM CreditGroup c ORDER BY c.creditLimit ASC");
        List<CreditGroup> cg = query.getResultList();
        return cg;
    }

    @Override
    public String deleteMemberDiscountGroup(Long id) {
        MemberDiscountGroup mdg = getMemberDiscountGroupById(id);
        System.out.print("###########################SLC" + mdg.getId());
        if (!mdg.getCustomers().isEmpty()) {
            System.out.print("######################## customer exist");
            return "customerExist";
        }
        try {
            entityManager.remove(mdg);
        } catch (Exception e) {
            System.out.print(e.getMessage());
        }
        return "success";

    }

    //--------------------------------------------------------------------------
    //                              Analytics
    //--------------------------------------------------------------------------
    @Override
    public List<Analytics> getAnalyticsClientCategory() {

        Analytics wsAnalytics = new Analytics();
        Query query = entityManager.createQuery("SELECT COUNT(s) FROM SalesLead s WHERE s.category LIKE '%WholeSale%'");
        Long x = (Long) query.getResultList().get(0);
        System.out.println("int x: " + x);
        wsAnalytics.setCategory("Wholesale");
        wsAnalytics.setValue((double) x);

        Analytics dsAnalytics = new Analytics();
        query = entityManager.createQuery("SELECT COUNT(s) FROM SalesLead s WHERE s.category LIKE '%DirectSale%'");
        Long y = (Long) query.getResultList().get(0);
        System.out.println("int y: " + y);
        dsAnalytics.setCategory("DirectSale");
        dsAnalytics.setValue((double) y);

        List<Analytics> list = new ArrayList<Analytics>();
        list.add(dsAnalytics);
        list.add(wsAnalytics);

        return list;
    }

    @Override
    public List<Analytics> getAnalyticsClientIndustry() {

        Query query = entityManager.createQuery("SELECT NEW merlion.crm.slcm.pojo.Analytics(s.industry, COUNT(s)) FROM SalesLead s GROUP BY s.industry");
        List<Analytics> list = query.getResultList();

        return list;
    }

    @Override
    public List<Analytics> getAnalyticsCustomerCategory() {

        Analytics wsAnalytics = new Analytics();
        Query query = entityManager.createQuery("SELECT COUNT(c) FROM Customer c WHERE c.category LIKE '%WholeSale%'");
        Long x = (Long) query.getResultList().get(0);
        System.out.println("int x: " + x);
        wsAnalytics.setCategory("Wholesale");
        wsAnalytics.setValue((double) x);

        Analytics dsAnalytics = new Analytics();
        query = entityManager.createQuery("SELECT COUNT(c) FROM Customer c WHERE c.category LIKE '%DirectSale%'");
        Long y = (Long) query.getResultList().get(0);
        System.out.println("int y: " + y);
        dsAnalytics.setCategory("DirectSale");
        dsAnalytics.setValue((double) y);

        List<Analytics> list = new ArrayList<Analytics>();
        list.add(dsAnalytics);
        list.add(wsAnalytics);

        return list;
    }

    @Override
    public List<Analytics> getAnalyticsCustomerIndustry() {

        Query query = entityManager.createQuery("SELECT NEW merlion.crm.slcm.pojo.Analytics(c.industry, COUNT(c)) FROM Customer c GROUP BY c.industry");
        List<Analytics> list = query.getResultList();

        return list;
    }

    @Override
    public List<Analytics> getAnalyticsRegionSales(Date start, Date end) {

        Timestamp startTS = new Timestamp(start.getTime());
        Timestamp endTS = new Timestamp(end.getTime() + (long)(1* 23 * 60 * 60 * 1000));
        
        Query query = entityManager.createNativeQuery("SELECT p.`PRODUCTTYPE`, a.country, SUM(i.QTYORDEREDCASE) FROM saleslead s, salesorder so, saleslead_address sa, address a, deliveryorder o, itemline i, salesorder_deliveryorder saor, deliveryorder_itemline orit, product p WHERE so.customer_id = s.id AND sa.`SalesLead_ID` = s.id AND sa.`shippingAddress_ID` = a.id AND saor.`SalesOrder_ID` = so.`ID` AND saor.`deliveryOrders_ID` = o.`ID` AND orit.`DeliveryOrder_ID` = o.`ID`AND orit.`itemLines_ID` = i.`ID` AND i.`PRODUCT_ID` = p.`ID` AND so.`SALESORDERSTATUS` LIKE '"+Consts.ORDER_SHIPPED+"' AND so.`SALESORDERDATE` >= '"+startTS.toString()+"' AND so.`SALESORDERDATE` <= '"+endTS.toString()+"' GROUP BY a.country, p.`PRODUCTTYPE`");

        List list = query.getResultList();

        List<Analytics> disaggregatedResults = new ArrayList<Analytics>();
        for (int i = 0; i < list.size(); i++) {
            Analytics a = new Analytics();

            Object[] arr = (Object[]) list.get(i);
            a.setCategory((String) arr[0]);
            a.setCategory2((String) arr[1]);
            a.setValue(((BigDecimal) arr[2]).doubleValue());

            disaggregatedResults.add(a);
        }

        // Get list of Analytics by country
        ArrayList<Analytics> byCountry = new ArrayList();
        for (Analytics a : disaggregatedResults) {
            boolean found = false;
            String country = a.getCategory2();
            for (Analytics b : byCountry) {
                if (b.getCategory().equals(country)) {
                    found = true;
                }
            }
            if (!found) {
                Analytics c = new Analytics();
                c.setCategory(country);
                c.setValue(0.0);
                byCountry.add(c);
                found = false;
            }
        }

        // Populate data
        for (Analytics a : disaggregatedResults) {
            for (Analytics b : byCountry) {
                if (a.getCategory2().equals(b.getCategory())) {
                    if (a.getCategory().equals("Classic Nuts")) {
                        b.setValue(b.getValue() + a.getValue());
                    }
                    if (a.getCategory().equals("Classic Fruits")) {
                        b.setValue2(b.getValue2() + a.getValue());
                    }
                    if (a.getCategory().equals("Premium Chocolate Nuts")) {
                        b.setValue3(b.getValue3() + a.getValue());
                    }
                    if (a.getCategory().equals("Premium Redcurrant Fruits")) {
                        b.setValue4(b.getValue4() + a.getValue());
                    }
                }
            }
        }

        return byCountry;
    }

    @Override
    public List<Analytics> getAnalyticsIndustrySales(Date start, Date end) {

        Timestamp startTS = new Timestamp(start.getTime());
        Timestamp endTS = new Timestamp(end.getTime() + (long)(1* 23 * 60 * 60 * 1000));
        
        //Query query = entityManager.createQuery("SELECT NEW merlion.crm.slcm.pojo.Analytics(l.product.productType, a.country, COUNT(l.qtyOrderedCase)) FROM Customer c, SalesOrder s, Address a, IN(s.orderLines) o, IN(o.itemLines) l WHERE s.customer = c AND c.billingAddress = a GROUP BY a.country, l.product.productType");
        Query query = entityManager.createNativeQuery("SELECT p.`PRODUCTTYPE`, s.industry, SUM(i.QTYORDEREDCASE) FROM saleslead s, salesorder so, saleslead_address sa, address a, deliveryorder o, itemline i, salesorder_deliveryorder saor, deliveryorder_itemline orit, product p WHERE so.customer_id = s.id AND sa.`SalesLead_ID` = s.id AND sa.`shippingAddress_ID` = a.id AND saor.`SalesOrder_ID` = so.`ID` AND saor.`deliveryOrders_ID` = o.`ID` AND orit.`DeliveryOrder_ID` = o.`ID`AND orit.`itemLines_ID` = i.`ID` AND i.`PRODUCT_ID` = p.`ID` AND so.`SALESORDERDATE` >= '"+ startTS.toString() +"' AND so.`SALESORDERDATE` <= '"+ endTS.toString() +"' AND so.`SALESORDERSTATUS` LIKE '"+ Consts.ORDER_SHIPPED +"' GROUP BY s.industry, p.`PRODUCTTYPE`");

        List list = query.getResultList();

        List<Analytics> disaggregatedResults = new ArrayList<Analytics>();
        for (int i = 0; i < list.size(); i++) {
            Analytics a = new Analytics();

            Object[] arr = (Object[]) list.get(i);
            a.setCategory((String) arr[0]);
            a.setCategory2((String) arr[1]);
            a.setValue(((BigDecimal) arr[2]).doubleValue());

            disaggregatedResults.add(a);
        }

        // Get list of Analytics by industry
        ArrayList<Analytics> byIndustry = new ArrayList();
        for (Analytics a : disaggregatedResults) {
            boolean found = false;
            String industry = a.getCategory2();
            for (Analytics b : byIndustry) {
                if (b.getCategory().equals(industry)) {
                    found = true;
                }
            }
            if (!found) {
                Analytics c = new Analytics();
                c.setCategory(industry);
                c.setValue(0.0);
                byIndustry.add(c);
                found = false;
            }
        }

        // Populate data
        for (Analytics a : disaggregatedResults) {
            for (Analytics b : byIndustry) {
                if (a.getCategory2().equals(b.getCategory())) {
                    if (a.getCategory().equals("Classic Nuts")) {
                        b.setValue(b.getValue() + a.getValue());
                    }
                    if (a.getCategory().equals("Classic Fruits")) {
                        b.setValue2(b.getValue2() + a.getValue());
                    }
                    if (a.getCategory().equals("Premium Chocolate Nuts")) {
                        b.setValue3(b.getValue3() + a.getValue());
                    }
                    if (a.getCategory().equals("Premium Redcurrant Fruits")) {
                        b.setValue4(b.getValue4() + a.getValue());
                    }
                }
            }
        }

        return byIndustry;
    }

    @Override
    public List<Analytics> getAnalyticsProductSales(Date start, Date end) {

        Query query = entityManager.createQuery("SELECT NEW merlion.crm.slcm.pojo.Analytics(p.productType, SUM(i.qtyOrderedCase)) FROM SalesOrder s, Product p, IN(s.deliveryOrders) o, IN(o.itemLines) i WHERE i.product = p AND s.salesOrderStatus LIKE :p1 AND s.salesOrderDate >=:p2 AND s.salesOrderDate <=:p3 GROUP BY p.productType");
        query.setParameter("p1", Consts.ORDER_SHIPPED)
             .setParameter("p2", start)
             .setParameter("p3", end);
        List<Analytics> list = query.getResultList();

        return list;
    }
    
    @Override
    public Double getMinimumMemberDiscountRate(){
        return (Double)entityManager.createQuery("SELECT MIN(m.memberDiscount) FROM MemberDiscountGroup m").getSingleResult();
    }
    
    @Override
    public Double getMaximumMemberDiscountRate(){
        return (Double)entityManager.createQuery("SELECT MAX(m.memberDiscount) FROM MemberDiscountGroup m").getSingleResult();
    }
    
    @Override
    public Double getMinimumOrderDiscountRate(){
        return (Double)entityManager.createQuery("SELECT MIN(o.orderDiscount) FROM OrderDiscountGroup o").getSingleResult();
    }
    
    @Override
    public Double getMaximumOrderDiscountRate(){
        return (Double)entityManager.createQuery("SELECT MAX(o.orderDiscount) FROM OrderDiscountGroup o").getSingleResult();
    }
    /*
    @Override
    public void weka() {
    InstanceQuery query = new InstanceQuery();
    query.setDatabaseURL("jdbc:mysql://localhost:3306/merlion");
    query.setUsername("root");
    query.setPassword("adminadmin");
    query.setQuery("SELECT s.industry, a.country, p.`PRODUCTTYPE` FROM saleslead s, salesorder so, saleslead_address sa, address a, orderline o, itemline i, salesorder_orderline saor, orderline_itemline orit, product p WHERE so.customer_id = s.id AND sa.`SalesLead_ID` = s.id AND sa.`shippingAddress_ID` = a.id AND saor.`SalesOrder_ID` = so.`ID` AND saor.`orderLines_ID` = o.`ID` AND orit.`OrderLine_ID` = o.`ID`AND orit.`itemLines_ID` = i.`ID` AND i.`PRODUCT_ID` = p.`ID` GROUP BY s.industry, p.`PRODUCTTYPE`");
    query.setSparseData(true);
    Instance train = query.retrieveInstances();
    data.setClassIndex(data.numAttributes() - 1);
    weka.classifiers.functions.SMO scheme = new weka.classifiers.functions.SMO();
    scheme.setOptions(weka.core.Utils.splitOptions("-C 1.0 -L 0.0010 -P 1.0E-12 -N 0 -V -1 -W 1 -K \"weka.classifiers.functions.supportVector.PolyKernel -C 250007 -E 1.0\""));
    // filter
    Remove rm = new Remove();
    rm.setAttributeIndices("1");  // remove 1st attribute
    // classifier
    J48 j48 = new J48();
    j48.setUnpruned(true);        // using an unpruned J48
    // meta-classifier
    FilteredClassifier fc = new FilteredClassifier();
    fc.setFilter(rm);
    fc.setClassifier(j48);
    // train and make predictions
    fc.buildClassifier(train);
    for (int i = 0; i < test.numInstances(); i++) {
    double pred = fc.classifyInstance(test.instance(i));
    System.out.print("ID: " + test.instance(i).value(0));
    System.out.print(", actual: " + test.classAttribute().value((int) test.instance(i).classValue()));
    System.out.println(", predicted: " + test.classAttribute().value((int) pred));
    }
    }
     */
    /*
    @Override
    public String deleteProductPrice(Product product) {
    try {
    product = entityManager.merge(product);
    entityManager.remove(product);
    entityManager.flush();
    return "deletedProductPrice";
    
    } catch (Exception e) {
    System.out.print(e.getMessage());
    return "error";
    }
    
    }
    
     */
}